const query = require('../sqlUtil/connect.js')
module.exports = {
    //根据id查找对应的详情页的介绍图片
    async queryDetailBannersById(goodId){
        return query.runSql(`select url from t_gooddetailbanner where goodId=${goodId}`)
    },
    //查询所有的食品类型
    async queryGoodById(goodId){
        return query.runSql(`select * from t_good where goodId=${goodId}`)
    },
    //查询所有的食品类型
    async queryAllType(){
        return query.runSql('select * from t_type')
    },
    //查询对应类型的所有食品
    async queryGoodsByType(type){
        return query.runSql(`select * from t_good where goodType=${type}`)
    },
    //查询对应商品的所有轮播图
    async queryBannersByGoodId(id){
        return query.runSql(`select * from t_goodbanner where goodId=${id}`)
    },
    //查询所有商品
    async queryAllGoods(id){
        return query.runSql('select * from t_good')
    }
    ,
    //上传新的商品
    async addNewGood(goodName,goodIcon,goodDesc,goodPrice,goodDiscount,goodDetailDesc,goodType,goodSpec){
        return query.runSql(`insert into t_good (goodName,goodIcon,goodDesc,goodPrice,goodDiscount,goodDetailDesc,goodType,goodSpec) values('${goodName}','${goodIcon}','${goodDesc}','${goodPrice}','${goodDiscount}','${goodDetailDesc}','${goodType}','${goodSpec}')`)
    }
    ,
    //更新旧的商品
    async updateGood(goodId,goodName,goodPrice,goodDiscount,goodDetailDesc,goodDesc,goodType,goodSpec){
        return query.runSql(`update t_good set goodName='${goodName}',goodDesc='${goodDesc}',goodPrice='${goodPrice}',goodDiscount='${goodDiscount}',goodDetailDesc='${goodDetailDesc}',goodType='${goodType}',goodSpec='${goodSpec}' where goodId=${goodId}`)
    },
    //获取刚刚插入的数据的id
    async getLastedId(){
        return query.runSql('SELECT MAX(goodId) as max FROM t_good')
    },
    //上传商品的轮播图
    async addNewBanner(goodId,url){
        return query.runSql(`insert into t_goodbanner (goodId,url) values('${goodId}','${url}')`)
    },
    //上传商品的详情页轮播图
    async addNewDetailBanner(goodId,url){
        return query.runSql(`insert into t_gooddetailbanner (goodId,url) values('${goodId}','${url}')`)
    }
    ,
    //上传新的商品类型
    async addNewType(typeName){
        return query.runSql(`insert into t_type (typeName) values('${typeName}')`)
    },
    //在数据库中删除对应id的商品
    async delGoodById(goodId){
        return query.runSql(`delete from t_good where goodId=${goodId}`)
    },
    //在数据库中删除对应商品所有的图片索引
    async delAllBannerById(goodId){
        return query.runSql(`DELETE t_goodbanner,t_gooddetailbanner FROM t_goodbanner LEFT JOIN t_gooddetailbanner ON t_goodbanner.goodId=t_gooddetailbanner.goodId WHERE t_goodbanner.goodId=${goodId}`)
    },
    //在数据库中查找出所有对应年月份的数据按照降序排序
    async getGoodRank(year,month){
        return query.runSql(`SELECT a.goodName,a.saleVolume FROM ((SELECT t1.goodName,t1.saleVolume FROM (SELECT * FROM t_ranks WHERE YEAR=${year} AND MONTH=${month}) AS t1 LEFT JOIN t_good AS t2 ON t1.goodId=t2.goodId) UNION 
        (SELECT t1.goodName,IFNULL(t2.saleVolume,0) AS saleVolume FROM t_good AS t1 LEFT JOIN (SELECT * FROM t_ranks WHERE YEAR=${year} AND MONTH=${month}) AS t2 ON t1.goodId=t2.goodId)) AS a ORDER BY a.saleVolume+0 DESC`)
    },
    //向数据库中增加新的排行榜记录
    async addRankRecord(goodId,goodName,saleVolume){
        return query.runSql(`INSERT INTO t_ranks (\`goodId\`,\`year\`,\`month\`,\`goodName\`,\`saleVolume\`) VALUE(${goodId},YEAR(CURDATE()),MONTH(CURDATE()),'${goodName}',${saleVolume})`)
    },
    //向数据库改变排行榜记录
    async updateRankRecord(goodId,year,month,saleVolume){
        return query.runSql(`UPDATE t_ranks SET saleVolume=saleVolume+${saleVolume} WHERE goodId=${goodId} AND YEAR=${year} AND MONTH=${month}`)
    },
}